package com.pai.dao.LeaveTicket;

import com.mysql.cj.util.StringUtils;
import com.pai.dao.BaseDao;
import com.pai.entity.LeaveTicket;
import com.pai.util.DateUtil;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class LeaveTicketDaoImpl implements LeaveTicketDao {
    DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
    /**
     * 获取列表
     * @param connection
     * @return
     * @throws Exception
     */
    @Override
    public List<LeaveTicket> getList(Connection connection) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<LeaveTicket> resultList = new ArrayList<LeaveTicket>();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from ((leaveticket left join personnel stu on leaveticket.studentId = stu.id) left join personnel col on leaveticket.counselorId = col.id ) LEFT JOIN department cla on stu.departmentId = cla.id ");



        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            LeaveTicket _item = new LeaveTicket();
            _item.setId(rs.getInt("leaveticket.id"));
            _item.setLeaveNo(rs.getString("leaveticket.leaveNo"));
            _item.setLeaveType(rs.getInt("leaveticket.leaveType"));
            _item.setReason(rs.getString("leaveticket.reason"));
            _item.setStarDate(rs.getDate("leaveticket.starDate"));
            _item.setEndDate(rs.getDate("leaveticket.endDate"));
            _item.setCreateDate(rs.getDate("leaveticket.createDate"));
            _item.setState(rs.getInt("leaveticket.state"));
            _item.setRemarks(rs.getString("leaveticket.remarks"));
            _item.setStudentId(rs.getInt("stu.id"));
            _item.setStudentName(rs.getString("stu.name"));
            _item.setCounselorId(rs.getInt("col.id"));
            _item.setCounselorName(rs.getString("col.name"));
            _item.setClassId(rs.getInt("cla.id"));
            _item.setClassName(rs.getString("cla.depName"));


            resultList.add(_item);
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return resultList;
    }

    /**
     * 获取列表
     * @param connection
     * @param claId  班级id
     * @param starDate  请假开始时间
     * @param endDate  请假结束时间
     * @return
     * @throws Exception
     */
    @Override
    public List<LeaveTicket> getList(Connection connection, String claId, Date starDate, Date endDate) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<LeaveTicket> resultList = new ArrayList<LeaveTicket>();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from ((leaveticket left join personnel stu on leaveticket.studentId = stu.id) left join personnel col on leaveticket.counselorId = col.id ) LEFT JOIN department cla on stu.departmentId = cla.id where cla.id = ? ");
        params.add(claId);
        if(starDate != null && endDate != null){
            sql.append(" and ((DATE(?) <= DATE(leaveticket.starDate) and DATE(leaveticket.starDate) <= DATE(?)) or (DATE(?) <= DATE(leaveticket.endDate) and DATE(leaveticket.starDate) <= DATE(?)))");
            params.add(starDate);
            params.add(endDate);
            params.add(starDate);
            params.add(endDate);
        }




        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            LeaveTicket _item = new LeaveTicket();
            _item.setId(rs.getInt("leaveticket.id"));
            _item.setLeaveNo(rs.getString("leaveticket.leaveNo"));
            _item.setLeaveType(rs.getInt("leaveticket.leaveType"));
            _item.setReason(rs.getString("leaveticket.reason"));
            _item.setStarDate(rs.getDate("leaveticket.starDate"));
            _item.setEndDate(rs.getDate("leaveticket.endDate"));
            _item.setCreateDate(rs.getDate("leaveticket.createDate"));
            _item.setState(rs.getInt("leaveticket.state"));
            _item.setRemarks(rs.getString("leaveticket.remarks"));
            _item.setStudentId(rs.getInt("stu.id"));
            _item.setStudentName(rs.getString("stu.name"));
            _item.setCounselorId(rs.getInt("col.id"));
            _item.setCounselorName(rs.getString("col.name"));
            _item.setClassId(rs.getInt("cla.id"));
            _item.setClassName(rs.getString("cla.depName"));


            resultList.add(_item);
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return resultList;
    }

    /**
     * 获取请假单列表（学生）
     * @param connection
     * @param stuId  学生id
     * @return
     * @throws Exception
     */
    @Override
    public List<LeaveTicket> getListStu(Connection connection, String stuId) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<LeaveTicket> resultList = new ArrayList<LeaveTicket>();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from ((leaveticket left join personnel stu on leaveticket.studentId = stu.id) left join personnel col on leaveticket.counselorId = col.id) LEFT JOIN department cla on stu.departmentId = cla.id where studentId = ?");
        params.add(stuId);

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            LeaveTicket _item = new LeaveTicket();
            _item.setId(rs.getInt("leaveticket.id"));
            _item.setLeaveNo(rs.getString("leaveticket.leaveNo"));
            _item.setLeaveType(rs.getInt("leaveticket.leaveType"));
            _item.setReason(rs.getString("leaveticket.reason"));
//            _item.setStarDate(new Date(rs.getString("leaveticket.starDate")));
//            _item.setEndDate(new Date(rs.getString("leaveticket.endDate")));
//            _item.setCreateDate(new Date(rs.getString("leaveticket.createDate")));
            _item.setStarDate(rs.getDate("leaveticket.starDate"));
            _item.setEndDate(rs.getDate("leaveticket.endDate"));
            _item.setCreateDate(rs.getDate("leaveticket.createDate"));
            _item.setState(rs.getInt("leaveticket.state"));
            _item.setRemarks(rs.getString("leaveticket.remarks"));
            _item.setStudentId(rs.getInt("stu.id"));
            _item.setStudentName(rs.getString("stu.name"));
            _item.setCounselorId(rs.getInt("col.id"));
            _item.setCounselorName(rs.getString("col.name"));
            _item.setClassId(rs.getInt("cla.id"));
            _item.setClassName(rs.getString("cla.depName"));


            resultList.add(_item);
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return resultList;
    }

    /**
     * 获取请假单列表（辅导员）
     * @param connection
     * @param colId  辅导员id
     * @return
     * @throws Exception
     */
    @Override
    public List<LeaveTicket> getListCol(Connection connection, String colId) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<LeaveTicket> resultList = new ArrayList<LeaveTicket>();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("SELECT * FROM ((leaveticket left join personnel stu on studentId = stu.id) left join department cla on stu.departmentId = cla.id) left join personnel col on leaveticket.counselorId = col.id where cla.principalId = ?");
        params.add(colId);

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            LeaveTicket _item = new LeaveTicket();
            _item.setId(rs.getInt("leaveticket.id"));
            _item.setLeaveNo(rs.getString("leaveticket.leaveNo"));
            _item.setLeaveType(rs.getInt("leaveticket.leaveType"));
            _item.setReason(rs.getString("leaveticket.reason"));
//            _item.setStarDate(new Date(rs.getString("leaveticket.starDate")));
//            _item.setEndDate(new Date(rs.getString("leaveticket.endDate")));
//            _item.setCreateDate(new Date(rs.getString("leaveticket.createDate")));
            _item.setStarDate(rs.getDate("leaveticket.starDate"));
            _item.setEndDate(rs.getDate("leaveticket.endDate"));
            _item.setCreateDate(rs.getDate("leaveticket.createDate"));
            _item.setState(rs.getInt("leaveticket.state"));
            _item.setRemarks(rs.getString("leaveticket.remarks"));
            _item.setStudentId(rs.getInt("stu.id"));
            _item.setStudentName(rs.getString("stu.name"));
            _item.setCounselorId(rs.getInt("col.id"));
            _item.setCounselorName(rs.getString("col.name"));
            _item.setClassId(rs.getInt("cla.id"));
            _item.setClassName(rs.getString("cla.depName"));


            resultList.add(_item);
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return resultList;
    }



    /**
     * 根据部门类型获取部门
     * @param connection
     * @param depType 0：教学部门（二级学院） 1：行政部门（学工处） 2：班级
     * @return
     * @throws Exception
     */
    @Override
    public List<LeaveTicket> getList(Connection connection, String depType) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<LeaveTicket> resultList = new ArrayList<LeaveTicket>();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from (department left join department farDep on department.parentId = farDep.id) left join personnel on department.principalId = personnel.id where department.depType = ? ");
        params.add(depType);



        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            LeaveTicket _item = new LeaveTicket();
            _item.setId(rs.getInt("leaveticket.id"));
            _item.setLeaveNo(rs.getString("leaveticket.leaveNo"));
            _item.setLeaveType(rs.getInt("leaveticket.leaveType"));
            _item.setReason(rs.getString("leaveticket.reason"));
            _item.setStarDate(rs.getDate("leaveticket.starDate"));
            _item.setEndDate(rs.getDate("leaveticket.endDate"));
            _item.setCreateDate(rs.getDate("leaveticket.createDate"));
            _item.setState(rs.getInt("leaveticket.state"));
            _item.setRemarks(rs.getString("leaveticket.remarks"));
            _item.setStudentId(rs.getInt("stu.id"));
            _item.setStudentName(rs.getString("stu.name"));
            _item.setCounselorId(rs.getInt("col.id"));
            _item.setCounselorName(rs.getString("col.name"));
            resultList.add(_item);
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return resultList;
    }

    /**
     * 获取班级部门列表
     * @param connection
     * @param depId 父级部门id
     * @return
     * @throws Exception
     */
    @Override
    public List<LeaveTicket> getDepClassList(Connection connection, String depId) throws Exception {

        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<LeaveTicket> resultList = new ArrayList<LeaveTicket>();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from (department left join department farDep on department.parentId = farDep.id) left join personnel on department.principalId = personnel.id where department.parentId = ? ");
        params.add(depId);




        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            LeaveTicket _item = new LeaveTicket();
            _item.setId(rs.getInt("leaveticket.id"));
            _item.setLeaveNo(rs.getString("leaveticket.leaveNo"));
            _item.setLeaveType(rs.getInt("leaveticket.leaveType"));
            _item.setReason(rs.getString("leaveticket.reason"));
            _item.setStarDate(rs.getDate("leaveticket.starDate"));
            _item.setEndDate(rs.getDate("leaveticket.endDate"));
            _item.setCreateDate(rs.getDate("leaveticket.createDate"));
            _item.setState(rs.getInt("leaveticket.state"));
            _item.setRemarks(rs.getString("leaveticket.remarks"));
            _item.setStudentId(rs.getInt("stu.id"));
            _item.setStudentName(rs.getString("stu.name"));
            _item.setCounselorId(rs.getInt("col.id"));
            _item.setCounselorName(rs.getString("col.name"));
            resultList.add(_item);
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return resultList;
    }

    /**
     * 获取列表数量
     * @param connection
     * @param date  创建日期
     * @return
     * @throws Exception
     */
    @Override
    public int getListCount(Connection connection, Date date) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        int count = 0;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("SELECT COUNT(id) as count FROM `leaveticket` where DATE(createDate) = DATE(?)");
        params.add(date);


        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            count = rs.getInt("count");
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return count;
    }

    /**
     * 获取项详情
     * @param connection
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public LeaveTicket getItemDetail(Connection connection, String id) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        LeaveTicket item = null;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from (leaveticket left join personnel stu on leaveticket.studentId = stu.id) left join personnel col on leaveticket.counselorId = col.id where leaveticket.id = ?" );
        params.add(id);

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            LeaveTicket _item = new LeaveTicket();
            _item.setId(rs.getInt("leaveticket.id"));
            _item.setLeaveNo(rs.getString("leaveticket.leaveNo"));
            _item.setLeaveType(rs.getInt("leaveticket.leaveType"));
            _item.setReason(rs.getString("leaveticket.reason"));
//            _item.setStarDate(new Date(rs.getString("leaveticket.starDate")));
//            _item.setEndDate(new Date(rs.getString("leaveticket.endDate")));
//            _item.setCreateDate(new Date(rs.getString("leaveticket.createDate")));
            _item.setStarDate(rs.getDate("leaveticket.starDate"));
            _item.setEndDate(rs.getDate("leaveticket.endDate"));
            _item.setCreateDate(rs.getDate("leaveticket.createDate"));
            _item.setState(rs.getInt("leaveticket.state"));
            _item.setRemarks(rs.getString("leaveticket.remarks"));
            _item.setStudentId(rs.getInt("stu.id"));
            _item.setStudentName(rs.getString("stu.name"));
            _item.setCounselorId(rs.getInt("col.id"));
            _item.setCounselorName(rs.getString("col.name"));
            item = _item;
        }
        BaseDao.closeResource(null, preparedStatement, rs);
        return item;
    }

    /**
     * 获取项
     * @param connection
     * @param depName  部门名称
     * @param depType  部门类型
     * @return
     * @throws Exception
     */
    @Override
    public LeaveTicket getItemDetail(Connection connection, String depName, String depType) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        LeaveTicket item = null;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from department");
        if(!StringUtils.isNullOrEmpty(depName)){
            sql.append(" where depName = ? ");
            params.add(depName);
        }
        if(!StringUtils.isNullOrEmpty(depType)){
            sql.append(" and depType = ? ");
            params.add(depType);
        }


        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            LeaveTicket _item = new LeaveTicket();
            _item.setId(rs.getInt("leaveticket.id"));
            _item.setLeaveNo(rs.getString("leaveticket.leaveNo"));
            _item.setLeaveType(rs.getInt("leaveticket.leaveType"));
            _item.setReason(rs.getString("leaveticket.reason"));
            _item.setStarDate(rs.getDate("leaveticket.starDate"));
            _item.setEndDate(rs.getDate("leaveticket.endDate"));
            _item.setCreateDate(rs.getDate("leaveticket.createDate"));
            _item.setState(rs.getInt("leaveticket.state"));
            _item.setRemarks(rs.getString("leaveticket.remarks"));
            _item.setStudentId(rs.getInt("stu.id"));
            _item.setStudentName(rs.getString("stu.name"));
            _item.setCounselorId(rs.getInt("col.id"));
            _item.setCounselorName(rs.getString("col.name"));
            item = _item;
        }
        BaseDao.closeResource(null, preparedStatement, rs);
        return item;
    }

    /**
     * 新增
     * @param connection
     * @param item
     * @return
     * @throws Exception
     */
    @Override
    public int add(Connection connection, LeaveTicket item) throws Exception {
        PreparedStatement preparedStatement = null;
        int rs = 0;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();

        sql.append("insert into leaveticket (leaveNo, leaveType, reason, starDate, endDate, createDate, state, remarks, studentId, counselorId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        params.add(item.getLeaveNo());
        params.add(item.getLeaveType());
        params.add(item.getReason());
//        params.add(dateFormat.format(item.getStarDate()));
//        params.add(dateFormat.format(item.getEndDate()));
//        params.add(dateFormat.format(item.getCreateDate()));
        params.add(item.getStarDate());
        params.add(item.getEndDate());
        params.add(item.getCreateDate());
        params.add(item.getState());
        params.add(item.getRemarks());
        params.add(item.getStudentId());
        if(item.getCounselorId() != 0) params.add(item.getCounselorId());
        else params.add(null);

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, sql.toString(), params.toArray());

        BaseDao.closeResource(null, preparedStatement, null);
        return rs;
    }



    /**
     * 更新
     * @param connection
     * @param item
     * @return
     * @throws Exception
     */
    @Override
    public int update(Connection connection, LeaveTicket item) throws Exception {
        PreparedStatement preparedStatement = null;
        int rs = 0;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();

        sql.append("update leaveticket set leaveNo = ?, leaveType = ?, reason = ?, starDate = ?, endDate = ?, createDate = ?, state = ?, remarks = ?, studentId = ?, counselorId = ? where id = ?");
        params.add(item.getLeaveNo());
        params.add(item.getLeaveType());
        params.add(item.getReason());
        params.add(item.getStarDate());
        params.add(item.getEndDate());
        params.add(item.getCreateDate());
        params.add(item.getState());
        params.add(item.getRemarks());
        params.add(item.getStudentId());
        if(item.getCounselorId() != 0) params.add(item.getCounselorId());
        else params.add(null);
        params.add(item.getId());

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, sql.toString(), params.toArray());

        BaseDao.closeResource(null, preparedStatement, null);
        return rs;
    }

    /**
     * 删除
     * @param connection
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public int delete(Connection connection, String id) throws Exception {
        PreparedStatement preparedStatement = null;
        int rs = 0;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();

        sql.append("delete from leaveticket where id = ?");
        params.add(id);


        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, sql.toString(), params.toArray());

        BaseDao.closeResource(null, preparedStatement, null);
        return rs;
    }
}
